package com.css.fxfzysx.modules.cztDataMange.repository.impl;

import com.css.fxfzysx.constants.FxfzConstants;
import com.css.fxfzysx.modules.cztDataMange.entity.CbhdInfoEntity;
import com.css.fxfzysx.modules.cztDataMange.repository.SdoBuildingsRepository;
import com.css.fxfzysx.modules.cztDataMange.vo.QueryBacInformationVo;
import com.css.fxfzysx.modules.vabStructure.vo.CrsEntiretyQueryParam;
import com.css.fxfzysx.modules.vabStructure.vo.CrsEntiretyResultVO;
import com.css.fxfzysx.util.CreateGson;
import com.css.fxfzysx.util.PlatformObjectUtils;
import com.css.fxfzysx.web.PlatformSessionContext;
import com.css.fxfzysx.zcpt.sys.entity.SUser;
import com.css.fxfzysx.zcpt.sys.service.SUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.*;

@Repository
public class SdoBuildingsRepositoryImpl implements SdoBuildingsRepository {

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private SUserService sUserService;

    @Override
    public void saveHsoBuildings(CbhdInfoEntity cbhdInfoEntity) {
        String sql = "insert into czt_building_history_damage(cbhd_id,intensity,damage,province,city,county,use0,structure_type,years," +
                "fortification,cs,create_user,create_time,del_flag,use1,structure_type1,number,param_value,data_type,basic_library,create_user_name)values(:cbhdId,:intensity,:damage,:province,:city,:county,:use0,:structureType,:years," +
                ":fortification,:cs,:createUser,:createTime,:delFlag,:use1,:structureType1,:number,:paramValue,:dataType,:basicLibrary,:createUserName)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(cbhdInfoEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateBuildings(CbhdInfoEntity cbhdInfoEntity) {
        String sql = "update czt_building_history_damage set intensity= :intensity,damage = :damage,province= :province,city= :city,county= :county,use0= :use0," +
                "structure_type= :structureType,years= :years,fortification= :fortification,cs= :cs,update_user= :updateUser,update_time= :updateTime,use1 =:use1,structure_type1= :structureType1,remarks= :remarks" +
                " where cbhd_id= '" + cbhdInfoEntity.getCbhdId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(cbhdInfoEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public boolean deleteBuildingsById(String id) {
        String sql = "update czt_building_history_damage set del_flag = '1' where number=?";
        return jdbcTemplate.update(sql, id) > 0;
    }

    @Override
    public CbhdInfoEntity getBuildingsById(String buildingsId) {
        String sql = "select * from czt_building_history_damage where del_flag='0' and cbhd_id='" + buildingsId + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CbhdInfoEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public HashMap<String, Object> queryBuildings(CbhdInfoEntity cbhdInfoEntity, int page, int size, List<String> roleList) {
//        StringBuilder sql = new StringBuilder("select s.* from (select *,row_number() over(partition by (number) order by (create_time))  as s1 from czt_building_history_damage) s where del_flag='0' and s1=1");
        StringBuilder sql = new StringBuilder("select * from czt_building_history_damage where del_flag = '0'");
        if (!roleList.contains(FxfzConstants.CZT_PRI_GL_ROLE) && !roleList.contains(FxfzConstants.CZT_COUN_GL_ROLE)) {
            sql.append(" and create_user='" + PlatformSessionContext.getUserID() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getIntensity())) {
            sql.append(" and intensity ='" + cbhdInfoEntity.getIntensity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getProvince())) {
            sql.append(" and province ='" + cbhdInfoEntity.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCity())) {
            sql.append(" and city ='" + cbhdInfoEntity.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCounty())) {
            sql.append(" and county ='" + cbhdInfoEntity.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getYears())) {
            sql.append(" and years ='" + cbhdInfoEntity.getYears() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUserName())) {
            sql.append(" and create_user_name like'%" + cbhdInfoEntity.getUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getDamage())) {
            sql.append(" and damage like '%" + cbhdInfoEntity.getDamage() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateStartTime())) {
            sql.append(" and create_time >= '" + cbhdInfoEntity.getCreateStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateEndTime())) {
            String endTime = cbhdInfoEntity.getCreateEndTime() + " 23:59:59";
            sql.append(" and create_time <='" + endTime + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType())) {
            sql.append(" and structure_type ='" + cbhdInfoEntity.getStructureType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType1())) {
            sql.append(" and structure_type1 ='" + cbhdInfoEntity.getStructureType1() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getFortification())) {
            sql.append(" and fortification ='" + cbhdInfoEntity.getFortification() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUse0())) {
            sql.append(" and use0 ='" + cbhdInfoEntity.getUse0() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUse1())) {
            sql.append(" and use1 ='" + cbhdInfoEntity.getUse1() + "'");
        }
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(QueryBacInformationVo.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        sql.append(orderSql).append(limitSql);
        List<CbhdInfoEntity> data = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbhdInfoEntity.class));
        for (CbhdInfoEntity datum : data) {
            if (PlatformObjectUtils.isNotEmpty(datum.getCreateUser())) {
                String user = sUserService.getSUser(datum.getCreateUser());
                SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
                datum.setUserName(sUser.getUserName());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getProvince()) && PlatformObjectUtils.isNotEmpty(datum.getCity()) && PlatformObjectUtils.isNotEmpty(datum.getCounty())) {
                StringBuilder region = new StringBuilder(datum.getProvince()).append("-").append(datum.getCity()).append("-").append(datum.getCounty());
                datum.setRegion(region.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getStructureType())) {
                StringBuilder st = new StringBuilder(datum.getStructureType());
                if (PlatformObjectUtils.isNotEmpty(datum.getStructureType1())) {
                    st.append("-" + datum.getStructureType1());
                }
                datum.setSt(st.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getUse0()) && PlatformObjectUtils.isNotEmpty(datum.getUse0())) {
                StringBuilder use = new StringBuilder(datum.getUse0()).append("-").append(datum.getUse0());
                datum.setUse(use.toString());
            }
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", data);
        return resultMap;
    }

    @Override
    public HashMap<String, Object> queryCrsBuildings(CrsEntiretyQueryParam cbhdInfoEntity, int page, int size) {
//        StringBuilder sql = new StringBuilder("select s.* from (select *,row_number() over(partition by (number) order by (create_time))  as s1 from czt_crs_calentirety_result) s where del_flag='0' and s1=1");
        StringBuilder sql = new StringBuilder("select * from czt_crs_calentirety_result where del_flag='0'");
        if (PlatformObjectUtils.isNotEmpty(cbhdInfoEntity.getCreateUserName())) {
            sql.append(" and create_user_name ='" + cbhdInfoEntity.getCreateUserName() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getProvince())) {
            sql.append(" and province ='" + cbhdInfoEntity.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCity())) {
            sql.append(" and city ='" + cbhdInfoEntity.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCounty())) {
            sql.append(" and county ='" + cbhdInfoEntity.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getYears())) {
            sql.append(" and years ='" + cbhdInfoEntity.getYears() + "'");
        }

        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateStartTime())) {
            sql.append(" and create_time >= '" + cbhdInfoEntity.getCreateStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateEndTime())) {
            String endTime = cbhdInfoEntity.getCreateEndTime() + " 23:59:59";
            sql.append(" and create_time <='" + endTime + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType())) {
            sql.append(" and structure_type ='" + cbhdInfoEntity.getStructureType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType1())) {
            sql.append(" and structure_type1 ='" + cbhdInfoEntity.getStructureType1() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getFortification())) {
            sql.append(" and fortification ='" + cbhdInfoEntity.getFortification() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getPurpose())) {
            sql.append(" and purpose ='" + cbhdInfoEntity.getPurpose() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getPurpose1())) {
            sql.append(" and purpose1 ='" + cbhdInfoEntity.getPurpose1() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateUserName())) {
            sql.append(" and create_user_name like'%" + cbhdInfoEntity.getCreateUserName() + "%'");
        }
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CrsEntiretyResultVO.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        StringBuilder orderSql = new StringBuilder("order by create_time desc");
        sql.append(orderSql).append(limitSql);
        List<CrsEntiretyResultVO> data = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CrsEntiretyResultVO.class));
        for (CrsEntiretyResultVO datum : data) {
            if (PlatformObjectUtils.isNotEmpty(datum.getCreateUserId())) {
                String user = sUserService.getSUser(datum.getCreateUserId());
                SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
                datum.setCreateUserName(sUser.getUserName());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getProvince()) && PlatformObjectUtils.isNotEmpty(datum.getCity()) && PlatformObjectUtils.isNotEmpty(datum.getCounty())) {
                StringBuilder region = new StringBuilder(datum.getProvince()).append("-").append(datum.getCity()).append("-").append(datum.getCounty());
                datum.setRegion(region.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getStructureType())) {
                StringBuilder st = new StringBuilder(datum.getStructureType());
                if (PlatformObjectUtils.isNotEmpty(datum.getStructureType1())) {
                    st.append("-" + datum.getStructureType1());
                }
                datum.setSt(st.toString());
            }
            /*if (PlatformObjectUtils.isNotEmpty(datum.getStructureType())){
                datum.setSt(datum.getStructureType());
            }*/
            if (PlatformObjectUtils.isNotEmpty(datum.getPurpose()) && PlatformObjectUtils.isNotEmpty(datum.getPurpose1())) {
                StringBuilder use = new StringBuilder(datum.getPurpose()).append("-").append(datum.getPurpose1());
                datum.setUse(use.toString());
            }
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", data);
        return resultMap;
    }

    @Override
    public List<CbhdInfoEntity> getBuildingsByNumber(String number) {
        StringBuilder sql = new StringBuilder("select * from czt_building_history_damage where del_flag='0' and number = '" + number + "'");
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getProvince())) {
//            sql.append(" and province ='" + cbhdInfoEntity.getProvince() + "'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCity())) {
//            sql.append(" and city ='" + cbhdInfoEntity.getCity() + "'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCounty())) {
//            sql.append(" and county ='" + cbhdInfoEntity.getCounty() + "'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCs())) {
//            sql.append(" and cs ='" + cbhdInfoEntity.getCs() + "'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getFortification())) {
//            sql.append(" and fortification ='" + cbhdInfoEntity.getFortification() + "'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUse0())) {
//            sql.append(" and use0 ='" + cbhdInfoEntity.getUse0() + "'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUse1())) {
//            sql.append(" and use1 ='" + cbhdInfoEntity.getUse1() + "'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType())) {
//            sql.append(" and structure_type ='" + cbhdInfoEntity.getStructureType()+"'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType1())) {
//            sql.append(" and structure_type1 ='" + cbhdInfoEntity.getStructureType1()+"'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getYears())) {
//            sql.append(" and years ='" + cbhdInfoEntity.getYears()+"'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateStartTime())) {
//            sql.append(" and create_time >= '" + cbhdInfoEntity.getCreateStartTime() + "'");
//        }
//        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateEndTime())) {
//            sql.append(" and create_time <='" + cbhdInfoEntity.getCreateEndTime()+"'");
//        }
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbhdInfoEntity.class));
    }

    @Override
    public void updateBuildingsForm(CbhdInfoEntity entity) {
        String sql = "update czt_building_history_damage set param_value= :paramValue,update_time = :updateTime,update_user= :updateUser where cbhd_id='" + entity.getCbhdId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void moveBasicLibraryByNumber(String number, String operate) {
        String sql = "update czt_building_history_damage set basic_library= ? where number = ?";
        jdbcTemplate.update(sql, operate, number);
    }
}
